
<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="UTF-8">
    <title>JS读取和导出excel示例</title>
    <meta name="description" content="使用sheetjs读取和导出excel示例">
    <style type="text/css">
        table {
            border-collapse: collapse;
        }
        th, td {
            border: solid 1px #6D6D6D;
            padding: 5px 10px;
        }
        .mt-sm {margin-top: 8px;}
        body {
            background: #f4f4f4;
            padding: 0;
            margin: 0;
        }
        .container {
            width: 1024px;
            margin: 0 auto;
            background: #fff;
            padding: 20px;
            min-height: 100vh;
        }
    </style>

    <!--  浏览器页签角标图片  -->
    <link rel="shortcut icon"  href="https://csdnimg.cn/public/favicon.ico" >


    <script type="text/javascript" src="../lib/jquery/2.1.1/jquery.min.js"></script>
    <script type="text/javascript" src="./js/dist/xlsx.core.min.js"></script>


    <script type="text/javascript" src="./js/exportExcel.js"></script>

</head>
<body>
<div class="container">
    <h1>JavaScript读取和导出excel示例（基于js-xlsx）</h1>
    <div>
        <a href="http://blog.haoji.me/js-excel.html" target="_blank">如何使用JavaScript实现纯前端读取和导出excel文件</a><br>
        <a href="http://oss.sheetjs.com/js-xlsx/" target="_blank">官网演示</a><br>
        <a href="http://demo.haoji.me/2017/02/08-js-xlsx/" target="_blank">其他演示</a><br>
        <a href="https://github.com/SheetJS/js-xlsx/" target="_blank">Github</a>
    </div>


    <h2>导出excel</h2>
    <div class="mt-sm" style="padding-bottom:40px;">
        <input type="button" onclick="myExcelUtil.exportTestData()" value="导出测试数据"/>
    </div>
    <!-- contenteditable 属性规定元素内容是否可编辑。如果元素未设置 contenteditable 属性，那么元素会从其父元素继承该属性。-->
    <div contenteditable="true">
        <table id="test_table_01">
            <tbody>
                <tr><th>姓名</th><th>性别</th><th>年龄</th><th>籍贯</th></tr>
                <tr><td>张三</td><td>男</td><td>18</td><td>广东惠州</td></tr>
                <tr><td>李四</td><td>女</td><td>22</td><td>河北石家庄</td></tr>
            </tbody>
        </table>
    </div>
    <br>
    <div class="mt-sm" style="padding-bottom:40px;">
        <input type="button" onclick="myExcelUtil.exportTableData('test_table_01')" value="导出excel"/> 上面读取的表格您可以直接编辑，编辑后点击保存即可导出excel文件。
    </div>



















    <h2>读取excel（仅读取第一个sheet）</h2>
    <div class="mt-sm">
        <input type="file" id="file" style="display:none;" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"/>
        <a href="javascript:selectFile()">加载本地excel文件</a>
        <a href="javascript:loadRemoteFile('./sample/sample.xlsx')">加载远程excel文件</a>
    </div>

    <p>结果输出：（下面表格可直接编辑导出）</p>
    <div id="result" contenteditable></div>

    <h2>导出excel</h2>
    <div class="mt-sm" style="padding-bottom:40px;">
        <input type="button" onclick="exportExcel()" value="保存"/> 上面读取的表格您可以直接编辑，编辑后点击保存即可导出excel文件。
    </div>

    <h2>导出带单元格合并的excel</h2>
    <input type="button" value="导出" onclick="exportSpecialExcel()"/>
</div>
<script type="text/javascript">

    function selectFile() {
        document.getElementById('file').click();
    }

    // 读取本地excel文件
    function readWorkbookFromLocalFile(file, callback) {
        var reader = new FileReader();
        reader.onload = function(e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, {type: 'binary'});
            if(callback) callback(workbook);
        };
        reader.readAsBinaryString(file);
    }

    // 从网络上读取某个excel文件，url必须同域，否则报错
    function readWorkbookFromRemoteFile(url, callback) {
        var xhr = new XMLHttpRequest();
        xhr.open('get', url, true);
        xhr.responseType = 'arraybuffer';
        xhr.onload = function(e) {
            if(xhr.status == 200) {
                var data = new Uint8Array(xhr.response)
                var workbook = XLSX.read(data, {type: 'array'});
                if(callback) callback(workbook);
            }
        };
        xhr.send();
    }

    // 读取 excel文件
    function outputWorkbook(workbook) {
        var sheetNames = workbook.SheetNames; // 工作表名称集合
        sheetNames.forEach(name => {
            var worksheet = workbook.Sheets[name]; // 只能通过工作表名称来获取指定工作表
            for(var key in worksheet) {
                // v是读取单元格的原始值
                console.log(key, key[0] === '!' ? worksheet[key] : worksheet[key].v);
            }
        });
    }

    function readWorkbook(workbook) {
        var sheetNames = workbook.SheetNames; // 工作表名称集合
        var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
        var csv = XLSX.utils.sheet_to_csv(worksheet);
        document.getElementById('result').innerHTML = csv2table(csv);
    }

    // 将csv转换成表格
    function csv2table(csv)
    {
        var html = '<table>';
        var rows = csv.split('\n');
        rows.pop(); // 最后一行没用的
        rows.forEach(function(row, idx) {
            var columns = row.split(',');
            columns.unshift(idx+1); // 添加行索引
            if(idx == 0) { // 添加列索引
                html += '<tr>';
                for(var i=0; i<columns.length; i++) {
                    html += '<th>' + (i==0?'':String.fromCharCode(65+i-1)) + '</th>';
                }
                html += '</tr>';
            }
            html += '<tr>';
            columns.forEach(function(column) {
                html += '<td>'+column+'</td>';
            });
            html += '</tr>';
        });
        html += '</table>';
        return html;
    }





    $(function() {
        document.getElementById('file').addEventListener('change', function(e) {
            var files = e.target.files;
            if(files.length == 0) return;
            var f = files[0];
            if(!/\.xlsx$/g.test(f.name)) {
                alert('仅支持读取xlsx格式！');
                return;
            }
            readWorkbookFromLocalFile(f, function(workbook) {
                readWorkbook(workbook);
            });
        });
        loadRemoteFile('http://demo.haoji.me/2017/02/08-js-xlsx/sample/test.xlsx');
    });

    function loadRemoteFile(url) {
        readWorkbookFromRemoteFile(url, function(workbook) {
            readWorkbook(workbook);
        });
    }


    function exportSpecialExcel() {
        var aoa = [
            ['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
            ['姓名', '性别', '年龄', '注册时间'],
            ['张三', '男', 18, new Date()],
            ['李四', '女', 22, new Date()]
        ];
        var sheet = XLSX.utils.aoa_to_sheet(aoa);
        sheet['!merges'] = [
            // 设置A1-C1的单元格合并
            {s: {r: 0, c: 0}, e: {r: 0, c: 2}}
        ];
        openDownloadDialog(sheet2blob(sheet), '单元格合并示例.xlsx');
    }
</script>
</body>
</html>